How to add WITH (NOLOCK) and handle Parameter Sniffing in Entity Framework

TLDR

• You can implement DbCommandInterceptor to intercept and dynamically modify SQL commands to inject WITH (NOLOCK) or OPTION (OPTIMIZE FOR UNKNOWN).
• WITH (NOLOCK) is suitable for reading data to avoid lock contention, but it is not recommended for transactional scenarios requiring data consistency.
• OPTION (OPTIMIZE FOR UNKNOWN) can effectively mitigate performance issues caused by SQL Server Parameter Sniffing.
• The interceptor should exclude INSERT, UPDATE, DELETE, and other transactional statements, and filter for precise queries like TOP(1) or TOP(2) to avoid misuse of hints.
• After implementing the interceptor, it must be registered to EF Core via DbContextOptionsBuilder.

WARNING

The implementation method in this article may be considered an Anti-Pattern in some modern scenarios. For more recommended alternatives (such as RCSI architecture and TagWith implementation), please refer to the latest comprehensive discussion: Introduction to RCSI and Improved Entity Framework Locking Hint Interceptor

Using Interceptor to intercept SQL commands

In Entity Framework, if you need to automatically add SQL hints to all queries, the most effective way is to use DbCommandInterceptor. This mechanism allows developers to intercept and modify the generated SQL string before EF executes the database operation.

When to encounter this problem

When a project is developed using Entity Framework and requires performance optimization for large-scale read operations (such as avoiding lock contention), or to resolve poor execution plans caused by SQL Server Parameter Sniffing, manually modifying every line of SQL is impractical. This is when an interceptor is suitable.

Implementing interceptor logic

By inheriting from DbCommandInterceptor and overriding methods such as ReaderExecuting and ScalarExecuting, you can dynamically inject SQL commands.

public class FixDbCommandInterceptor : DbCommandInterceptor {
    private static readonly RegexOptions regexOptions = RegexOptions.Multiline | RegexOptions.IgnoreCase;
    private static readonly Regex cudRegex = new(@"\b(INSERT|UPDATE|DELETE)\b", regexOptions);
    private static readonly Regex tableAliasRegex = new(
        @"(?<tableAlias>(FROM|JOIN)\s+\[\w+\]\s+AS\s+\[\w+\])",
        regexOptions
    );

    public override InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command, CommandEventData eventData,
        InterceptionResult<DbDataReader> result) {
        FixCommand(command);
        return base.ReaderExecuting(command, eventData, result);
    }

    public override async ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(
        DbCommand command, CommandEventData eventData,
        InterceptionResult<DbDataReader> result,
        CancellationToken cancellationToken = default
    ) {
        FixCommand(command);
        return await base.ReaderExecutingAsync(command, eventData, result, cancellationToken);
    }

    private static void FixCommand(IDbCommand command) {
        string commandText = command.CommandText;

        // Exclude transactional syntax
        if (cudRegex.IsMatch(commandText)) {
            return;
        }

        // Exclude precise query scenarios to avoid accidental NOLOCK
        if (!commandText.Contains("TOP(1)") && !commandText.Contains("TOP(2)")) {
            commandText = tableAliasRegex.Replace(commandText, "${tableAlias} WITH (NOLOCK)");
        }

        // Inject Parameter Sniffing handling hint
        commandText = commandText.TrimEnd(';') + " OPTION (OPTIMIZE FOR UNKNOWN);";

        command.CommandText = commandText;
    }
}

Registration and Application

Once implemented, the interceptor must be registered in the DbContext to take effect.

Registration methods

• Register inside DbContext:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder.AddInterceptors(new FixDbCommandInterceptor());

• Register in DI container:

services.AddDbContext<TestDbContext>(options => {
    options
        .UseSqlServer(DbConnectionString)
        .AddInterceptors(new FixDbCommandInterceptor());
});

Execution result verification

With the interceptor above, when executing queries like ToList(), the SQL syntax will automatically be injected with hints; for precise queries like Find() or SingleOrDefault(), the NOLOCK hint will be automatically filtered out, leaving only OPTIMIZE FOR UNKNOWN.

-- ToList() execution result
SELECT [t].[Id], [t].[TestBit], [t].[TestDateTime], [t].[TestGuid], [t].[TestInt], [s].[Id], [s].[TestId]
FROM [Test] AS [t] WITH (NOLOCK)
LEFT JOIN [SubTest] AS [s] WITH (NOLOCK) ON [t].[Id] = [s].[TestId]
ORDER BY [t].[Id] OPTION (OPTIMIZE FOR UNKNOWN);

TIP

The logic for handling NOLOCK in the implementation above is relatively basic. If your project contains complex subqueries or special SQL structures, it is recommended to further refine the Regex rules to ensure syntax correctness.

Revision History

• 2024-07-18 Initial document created.